package org.sysdance.aplicativo.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.LinkedList;
import java.util.NoSuchElementException;

import org.sysdance.aplicativo.entidade.Aluno;
import org.sysdance.aplicativo.enums.StatusAlunoEnum;
import org.sysdance.aplicativo.excecao.ConexaoException;
import org.sysdance.aplicativo.excecao.DAOException;
import org.sysdance.aplicativo.integracao.AlunoIntegracao;
import org.sysdance.aplicativo.util.ConexaoUtil;
import org.sysdance.aplicativo.util.ResultSetUtil;
import org.sysdance.aplicativo.util.SysDanceUtil;

public class AlunoDAO implements AlunoIntegracao{

	@Override
	public Aluno inserirAluno(Aluno aluno) {
		final StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO aluno(id_aluno, id_cidade,matricula_aluno, nm_aluno,");
        sql.append("cpf_aluno, dt_nascimento_aluno, sexo_aluno, logradouro_aluno,");
        sql.append("nmr_endereco_aluno, bairro_endereco_aluno, tel_aluno, cel_aluno,st_aluno) ");
        sql.append("VALUES (NEXTVAL('aluno_id_aluno_seq'), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ,?);");
        try {
            final Connection conexao = ConexaoUtil.criarNovaConexao();
            final PreparedStatement ps = conexao.prepareStatement(sql.toString(),new String[]{"id_aluno"});
            ps.setInt(1, aluno.getCidade().getIdCidade());
            aluno.setMatriculaAluno(this.criarNovaMatricula());
            ps.setInt(2, aluno.getMatriculaAluno());
            ps.setString(3, aluno.getNmAluno());
            ps.setString(4,aluno.getCpfAluno());
            ps.setDate(5, new Date(aluno.getDtNascimentoAluno().getTime()));
            ps.setInt(6, aluno.getSexoAluno().getValor());
            ps.setString(7,aluno.getLogradouroAluno());
            ps.setString(8, aluno.getNmrEnderecoAluno());
            ps.setString(9, aluno.getBairroEnderecoAluno());
            ps.setString(10, aluno.getTelAluno());
            ps.setString(11,aluno.getCelAluno());
            ps.setInt(12, aluno.getStAlunoEnum().getValor());
            ps.executeUpdate();
            final ResultSet rs = ps.getGeneratedKeys();
            if(rs.next()){
                aluno.setIdAluno(rs.getInt(rs.getMetaData().getColumnName(1)));
            }
        } catch (final ConexaoException ex) {
            SysDanceUtil.getInstancia().imprimirLog(ex);
        } catch (final SQLException sqlex) {
            SysDanceUtil.getInstancia().imprimirLog(sqlex);
        }finally{
        	try {
				ConexaoUtil.fecharConexao();
			} catch (ConexaoException e) {
				SysDanceUtil.getInstancia().imprimirLog(e);
			}
        }
        return aluno;
	}
	
	public Integer criarNovaMatricula(){
		final StringBuilder sql = new StringBuilder();
        sql.append("SELECT NEXTVAL('matricula_aluno_seq') as nova_matricula;");
        try {
            final Connection conexao = ConexaoUtil.criarNovaConexao();
            final PreparedStatement ps = conexao.prepareStatement(sql.toString());
            final ResultSet rs = ps.executeQuery();
            if(rs.next()){
                return rs.getInt("nova_matricula");
            }
        } catch (final ConexaoException ex) {
            SysDanceUtil.getInstancia().imprimirLog(ex);
        } catch (final SQLException sqlex) {
            SysDanceUtil.getInstancia().imprimirLog(sqlex);
        }finally{
        	try {
				ConexaoUtil.fecharConexao();
			} catch (ConexaoException e) {
				SysDanceUtil.getInstancia().imprimirLog(e);
			}
        }
        return null;
	}
	
	@Override
	public void vincularAlunoResponsavel(Integer idAluno, Integer idResponsavel) {
		final StringBuilder sql = new StringBuilder();
        sql.append("UPDATE aluno SET id_responsavel=? WHERE id_aluno=?;");
        try {
            final Connection conexao = ConexaoUtil.criarNovaConexao();
            final PreparedStatement ps = conexao.prepareStatement(sql.toString());
            ps.setObject(1, idResponsavel);
            ps.setObject(2, idAluno);
            ps.executeUpdate();
        } catch (final ConexaoException ex) {
            SysDanceUtil.getInstancia().imprimirLog(ex);
        } catch (final SQLException sqlex) {
            SysDanceUtil.getInstancia().imprimirLog(sqlex);
        }finally{
        	try {
				ConexaoUtil.fecharConexao();
			} catch (ConexaoException e) {
				SysDanceUtil.getInstancia().imprimirLog(e);
			}
        }
	}
	
	@Override
	public Collection<Aluno> consultarTodosAlunos() {
		final StringBuilder sql = new StringBuilder();
        sql.append("SELECT * FROM aluno;");
        try {
            final Connection conexao = ConexaoUtil.criarNovaConexao();
            final PreparedStatement ps = conexao.prepareStatement(sql.toString());
            return ResultSetUtil.extrair(ps.executeQuery(), Aluno.class);
        } catch (final ConexaoException ex) {
            SysDanceUtil.getInstancia().imprimirLog(ex);
        } catch (final SQLException sqlex) {
            SysDanceUtil.getInstancia().imprimirLog(sqlex);
        }finally{
        	try {
				ConexaoUtil.fecharConexao();
			} catch (ConexaoException e) {
				SysDanceUtil.getInstancia().imprimirLog(e);
			}
        }
        return new LinkedList<Aluno>();
	}
	
	@Override
	public Aluno consultarAluno(Integer matricula, String cpf) {
		final StringBuilder sql = new StringBuilder();
        sql.append("SELECT * FROM aluno WHERE ");
    	sql.append("matricula_aluno=? or cpf_aluno=?;");
        try {
            final Connection conexao = ConexaoUtil.criarNovaConexao();
            final PreparedStatement ps = conexao.prepareStatement(sql.toString());
            ps.setInt(1, matricula);
            ps.setString(2, cpf);
            return ResultSetUtil.extrair(ps.executeQuery(), Aluno.class).iterator().next();
        } catch (final ConexaoException ex) {
            SysDanceUtil.getInstancia().imprimirLog(ex);
        } catch (final SQLException sqlex) {
            SysDanceUtil.getInstancia().imprimirLog(sqlex);
        }catch(NoSuchElementException s){
        	return null;
        }finally{
        	try {
				ConexaoUtil.fecharConexao();
			} catch (ConexaoException e) {
				SysDanceUtil.getInstancia().imprimirLog(e);
			}
        }
        return null;
	}
	
	
	@Override
	public Collection<Aluno> consultarAlunosPorCursoTurma(Integer curso,Integer turma) {
		final StringBuilder sql = new StringBuilder();
        sql.append("SELECT DISTINCT(aluno.id_aluno) , aluno.* FROM aluno, aluno_turma_curso ");
        sql.append("WHERE aluno.id_aluno = aluno_turma_curso.id_aluno AND ");
        sql.append("aluno_turma_curso.id_aluno = aluno.id_aluno AND ");
        sql.append("aluno_turma_curso.id_curso = ?              AND ");
        sql.append("aluno_turma_curso.id_turma = ?              AND ");
        sql.append("aluno.st_aluno <> " + StatusAlunoEnum.INATIVO.getValor());
        try {
            final Connection conexao = ConexaoUtil.criarNovaConexao();
            final PreparedStatement ps = conexao.prepareStatement(sql.toString());
            ps.setInt(1, curso);
            ps.setInt(2,turma);
            return ResultSetUtil.extrair(ps.executeQuery(), Aluno.class);
        } catch (final ConexaoException ex) {
            SysDanceUtil.getInstancia().imprimirLog(ex);
        } catch (final SQLException sqlex) {
            SysDanceUtil.getInstancia().imprimirLog(sqlex);
        }finally{
        	try {
				ConexaoUtil.fecharConexao();
			} catch (ConexaoException e) {
				SysDanceUtil.getInstancia().imprimirLog(e);
			}
        }
        return new LinkedList<Aluno>();
	}
	
	@Override
	public Boolean isExisteCPFCadastrado(String cpfAluno) {
		final StringBuilder sql = new StringBuilder();
        sql.append("SELECT cpf_aluno FROM aluno WHERE cpf_aluno=?;");
        try {
            final Connection conexao = ConexaoUtil.criarNovaConexao();
            final PreparedStatement ps = conexao.prepareStatement(sql.toString());
            ps.setString(1, cpfAluno);
            return ps.executeQuery().next();
        } catch (final ConexaoException ex) {
            SysDanceUtil.getInstancia().imprimirLog(ex);
        } catch (final SQLException sqlex) {
            SysDanceUtil.getInstancia().imprimirLog(sqlex);
        }finally{
        	try {
				ConexaoUtil.fecharConexao();
			} catch (ConexaoException e) {
				SysDanceUtil.getInstancia().imprimirLog(e);
			}
        }
        return Boolean.FALSE;
	}
	
	@Override
	public void atualizarDadosAluno(Aluno aluno) throws DAOException {
		final StringBuilder sql = new StringBuilder();
        sql.append("UPDATE aluno SET id_cidade=?, id_responsavel=?, nm_aluno=?, cpf_aluno=?, dt_nascimento_aluno=?, sexo_aluno=?,");
        sql.append("logradouro_aluno=?, nmr_endereco_aluno=?, bairro_endereco_aluno=?,");
        sql.append("tel_aluno=?, cel_aluno=?, st_aluno=? ");
        sql.append("WHERE id_aluno=?;");
        try {
            final Connection conexao = ConexaoUtil.criarNovaConexao();
            final PreparedStatement ps = conexao.prepareStatement(sql.toString());
            ps.setInt(1, aluno.getIdCidade());
            ps.setObject(2,aluno.getIdResponsavel());
            ps.setString(3, aluno.getNmAluno());
            ps.setString(4, aluno.getCpfAluno());
            ps.setDate(5, new Date(aluno.getDtNascimentoAluno().getTime()));
            ps.setInt(6, aluno.getSexoAluno().getValor());
            ps.setString(7, aluno.getLogradouroAluno());
            ps.setString(8, aluno.getNmrEnderecoAluno());
            ps.setString(9, aluno.getBairroEnderecoAluno());
            ps.setString(10, aluno.getTelAluno());
            ps.setString(11, aluno.getCelAluno());
            ps.setInt(12, aluno.getStAlunoEnum().getValor());
            ps.setInt(13, aluno.getIdAluno());
            ps.executeUpdate();
        } catch (final ConexaoException ex) {
            throw new DAOException(ex.getMessage());
        } catch (final SQLException sqlex) {
            throw new DAOException(sqlex.getMessage());
        }finally{
        	try {
				ConexaoUtil.fecharConexao();
			} catch (ConexaoException e) {
				SysDanceUtil.getInstancia().imprimirLog(e);
			}
        }
		
	}
	
}
